﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;

namespace com.lover.dbcommon
{
    public class SqliteHelper
    {
        private static string _defaultConnString = "";
        private static SqliteHelper _defaultInstance;
        public static SqliteHelper getInstance(string connString = null)
        {
            if (connString == null)
            {
                if (_defaultInstance == null) _defaultInstance = new SqliteHelper(_defaultConnString);
                return _defaultInstance;
            }
            else return new SqliteHelper(connString);
        }
        private string _connString;
        private SqliteHelper(string connString) { _connString = connString; }
        public int exeNonQuery(string cmdText, SQLiteParameter[] cmdParams = null)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(_connString))
            {
                prepareCommand(cmd, conn, cmdText, cmdParams);
                int lines = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return lines;
            }
        }
        public object exeScalar(string cmdText, SQLiteParameter[] cmdParams = null)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(_connString))
            {
                prepareCommand(cmd, conn, cmdText, cmdParams);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        public SQLiteDataReader exeReader(string cmdText, SQLiteParameter[] cmdParams = null)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(_connString))
                try
                {
                    prepareCommand(cmd, conn, cmdText, cmdParams);
                    SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return reader;
                }
                catch
                {
                    if (conn != null) conn.Close();
                    throw;
                }
        }
        public SQLiteParameter createParameter(string pName, object value, DbType dbType, int size = 0, ParameterDirection direction = ParameterDirection.Input)
        {
            SQLiteParameter p;
            if (size > 0) p = new SQLiteParameter(pName, dbType, size);
            else p = new SQLiteParameter(pName, dbType);

            p.Direction = direction;
            if (!(direction == ParameterDirection.Output && value == null)) p.Value = value;

            return p;
        }
        private void prepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, SQLiteParameter[] cmdParams = null)
        {
            if (conn.State != ConnectionState.Open) { conn.Open(); }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (cmdParams != null && cmdParams.Length > 0) { foreach (SQLiteParameter p in cmdParams) cmd.Parameters.Add(p); }
        }
    }
}
